package com.ygq;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * ExcelWriteBigDataTest
 *
 * @author Yin Guiqing
 */
public class ExcelWriteBigDataTest {
    static final String PATH = "src\\";

    /**
     * xls 文件的大数据写测试
     * <p>
     * xls 最多 65536 行，不然会报错
     */
    @Test
    public void testWrite2003() throws IOException {
        long start = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // HSSF 最多 65536 行，不然会报错
        // java.lang.IllegalArgumentException:
        // Invalid row number (65536) outside allowable range (0..65535)
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int colNum = 0; colNum < 10; colNum++) {
                row.createCell(colNum).setCellValue(colNum);
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "大数据2003.xls");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();

        long end = System.currentTimeMillis();
        System.out.println("【大数据2003.xls】生成完毕！");
        System.out.println("用时 " + (end - start) / 1000.0 + " 秒");
    }

    /**
     * xlsx 文件的大数据写测试
     * <p>
     * xlsx 最多 1048576 行
     */
    @Test
    public void testWrite2007() throws IOException {
        long start = System.currentTimeMillis();

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // XSSF 最多 1048576 行
        for (int rowNum = 0; rowNum < 65539; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int colNum = 0; colNum < 10; colNum++) {
                row.createCell(colNum).setCellValue(colNum);
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "大数据2007.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();

        long end = System.currentTimeMillis();
        System.out.println("【大数据2007.xlsx】生成完毕！");
        System.out.println("用时 " + (end - start) / 1000.0 + " 秒");
    }

    /**
     * xlsx 文件的大数据写测试 Stream 版
     * <p>
     * xlsx 最多 1048576 行
     * <p>
     * SXSSFWorkbook 是 XSSFWorkbook 的 Streaming version。
     */
    @Test
    public void testWrite2007Stream() throws IOException {
        long start = System.currentTimeMillis();

        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // XSSF 最多 1048576 行
        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int colNum = 0; colNum < 10; colNum++) {
                row.createCell(colNum).setCellValue(colNum);
            }
        }

        FileOutputStream outputStream
                = new FileOutputStream(PATH + "大数据2007Stream.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();

        long end = System.currentTimeMillis();
        System.out.println("【大数据2007Stream.xlsx】生成完毕！");
        System.out.println("用时 " + (end - start) / 1000.0 + " 秒");
        // SXSSF 会产生临时文件，清理掉
        workbook.dispose();
    }
}
